﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlServerCe;
using System.Configuration;

namespace SWEN3Application.Classes
{
    public class BookingDBManager
    {
        public static int CheckIn(BookingList details)
        {
            SqlCeConnection conn = null;
            int BookingNo = 0;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO BookingList(LastName,FirstName,AdultsNo,ChildsNo,PhoneNo,EmailAddress,Address,Remarks,Country)" +
                    " VALUES (@LastName,@FirstName,@AdultsNo,@ChildsNo,@PhoneNo,@EmailAddress,@Address,@Remarks,@Country)";
                comm.Parameters.AddWithValue("@LastName", details.LastName);
                comm.Parameters.AddWithValue("@FirstName", details.FirstName);
                comm.Parameters.AddWithValue("@AdultsNo", details.AdultsNo);
                comm.Parameters.AddWithValue("@ChildsNo", details.ChildsNo);
                comm.Parameters.AddWithValue("@PhoneNo", details.PhoneNo);
                comm.Parameters.AddWithValue("@EmailAddress", details.EmailAddress);
                comm.Parameters.AddWithValue("@Address", details.Address);
                comm.Parameters.AddWithValue("@Remarks", details.Remarks);
                comm.Parameters.AddWithValue("@Country", details.Country);
                if (comm.ExecuteNonQuery() > 0)
                {
                    SqlCeCommand comm2 = new SqlCeCommand();
                    comm2.Connection = conn;
                    comm2.CommandText = "SELECT * FROM BookingList WHERE BookingNo IN (SELECT MAX(BookingNo) FROM BookingList)";
                    comm2.ExecuteNonQuery();
                    SqlCeDataReader dr = comm2.ExecuteReader();
                    if (dr.Read())
                    {
                        BookingNo = (int)dr["BookingNo"];
                    }
                    dr.Close();
                }
            }
            catch (Exception e)
            {
                return 0;
            }
            finally
            {
                conn.Close();
            }
            return BookingNo;
        }

        public static LinkedList<BookingList> GetAllBooking()
        {
            SqlCeConnection conn = null;
            LinkedList<BookingList> ll = new LinkedList<BookingList>();
            BookingList b = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from BookingList";
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");
                while (dr.Read())
                {
                    b = new BookingList();
                    b.BookingNo = (int)dr["BookingNo"];
                    b.LastName = (String)dr["LastName"];
                    b.FirstName = (String)dr["FirstName"];
                    b.AdultsNo = (int)dr["AdultsNo"];
                    b.ChildsNo = (int)(dr["ChildsNo"]);
                    b.PhoneNo = (String)(dr["PhoneNo"]);
                    b.EmailAddress = (String)(dr["EmailAddress"]);
                    b.Address = (String)(dr["Address"]);
                    b.Country = (String)(dr["Country"]);
                    b.Remarks = (String)(dr["Remarks"]);
                    ll.AddFirst(b);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return ll;
        }

        public static BookingList GetBookingByBookingNo(int BookingNo)
        {
            SqlCeConnection conn = null;
            BookingList b = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from BookingList WHERE BookingNo = @BookingNo";
                comm.Parameters.AddWithValue("@BookingNo", BookingNo);
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");
                if (dr.Read())
                {
                    b = new BookingList();
                    b.BookingNo = (int)dr["BookingNo"];
                    b.LastName = (String)dr["LastName"];
                    b.FirstName = (String)dr["FirstName"];
                    b.AdultsNo = (int)dr["AdultsNo"];
                    b.ChildsNo = (int)(dr["ChildsNo"]);
                    b.PhoneNo = (String)(dr["PhoneNo"]);
                    b.EmailAddress = (String)(dr["EmailAddress"]);
                    b.Address = (String)(dr["Address"]);
                    b.Country = (String)(dr["Country"]);
                    b.Remarks = (String)(dr["Remarks"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return b;
        }

        public static Boolean UpdateBookingDetails(BookingList details)
        {
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE BookingList SET LastName=@LastName, FirstName=@FirstName, AdultsNo=@AdultsNo, ChildsNo=@ChildsNo, PhoneNo=@PhoneNo, EmailAddress=@EmailAddress, Address=@Address, Remarks=@Remarks, Country=@Country WHERE BookingNo=@BookingNo";
                comm.Parameters.AddWithValue("@BookingNo", details.BookingNo);
                comm.Parameters.AddWithValue("@LastName", details.LastName);
                comm.Parameters.AddWithValue("@FirstName", details.FirstName);
                comm.Parameters.AddWithValue("@AdultsNo", details.AdultsNo);
                comm.Parameters.AddWithValue("@ChildsNo", details.ChildsNo);
                comm.Parameters.AddWithValue("@PhoneNo", details.PhoneNo);
                comm.Parameters.AddWithValue("@EmailAddress", details.EmailAddress);
                comm.Parameters.AddWithValue("@Address", details.Address);
                comm.Parameters.AddWithValue("@Remarks", details.Remarks);
                comm.Parameters.AddWithValue("@Country", details.Country);
                if (comm.ExecuteNonQuery() == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static int DeleteBookingByBookingNo(int BookingNo)
        {
            int rowsdeleted = 0;

            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from BookingList where BookingNo=@BookingNo";
                comm.Parameters.AddWithValue("@BookingNo", BookingNo);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
    }
}